﻿#include"database.h"
#pragma execution_character_set("utf-8")//设置中文

DataBase::DataBase(){
  OwnerTHead<<"buildingid"<<"address"<<"name"<<"sex"<<"age"<<"tel"
      <<"temperature"<<"healthCondition"
      <<"account"<<"password";
  StaffTHead<<"id"<<"name"<<"sex"<<"age"<<"tel"<<"temperature"<<"healthCondition"
      <<"account"<<"password";
  IOTHead<<"date"<<"name"<<"tel"<<"intime"<<"outtime"<<"temperature"<<"buildingid";
  MemberTHead<<"buildingid"<<"address"<<"ownername"<<"name"<<"sex"<<"age"<<"tel"
       <<"temperature"<<"healthCondition";
  ShoppingTHead<<"buildingid"<<"address"<<"ownername"<<"tel"<<"name"<<"number"
         <<"ordertime"<<"arrivetime"<<"statu";
}

//创建数据库
bool DataBase::createConnection()
{
    QTextCodec *code = QTextCodec::codecForName("UTF-8");
    QTextCodec::setCodecForLocale(code);
    QSqlDatabase db;
    if(!QSqlDatabase::contains("sqlite1.db"))
    {
    db = QSqlDatabase::addDatabase("QSQLITE");//数据库不存在则创建数据库db,并命名为sqlite1
    db.setDatabaseName("sqlite1.db");
    }
    else
    {
        db = QSqlDatabase::database("sqlite1.db");
    }
    if( !db.open())
    {
        qDebug() << "Fail to connect database";
        return false;//创建失败
    }
    return true;
}

//创建表
bool DataBase::createTable()
{
    QSqlDatabase db = QSqlDatabase::database("sqlite1.db");
    bool success = true;

    //创建业主表
    QString create_owner = "create table Owner(buildingid varchar(10), address varchar(10), name varchar(20), sex varchar(10), age int, "
                           "tel varchar(20), temperature double, healthCondition varchar(10), account varchar(10), password varchar(20))";
    //业主表属性排序: 楼栋号 门牌号 姓名 性别 年龄 联系方式 体温 健康状况 账户 密码, 相关函数输入参数顺序与之相同
    QSqlQuery query_owner(db);
    query_owner.prepare(create_owner);
    if(!query_owner.exec())
    {
        qDebug() << "创建业主表失败!";
        success = false;
    }

    //创建物业工作人员表
    QString create_stuff = "create table Staff(id varchar(10) , name varchar(10), sex varchar(10), age int, tel varchar(20), "
                           "temperature double, healthCondition varchar(10), account varchar(10), password varchar(20))";
    //物业工作人员表属性排序: 编号(负责楼栋) 姓名 性别 年龄 联系方式 体温 健康状况 账户 密码, 相关函数输入参数顺序与之相同
    QSqlQuery query_stuff(db);
    query_stuff.prepare(create_stuff);
    if(!query_stuff.exec())
    {
        qDebug() << "创建物业工作人员表失败!";
        success = false;
    }

    //创建进出小区登记表
    QString create_IO = "create table IO(date varchar(20), name varchar(20), tel varchar(20), intime varcahr(10), outtime varchar(10),"
                        "temperature double,buildingid varcahr(10))";
    //进出小区登记表属性排序: 日期 姓名 联系方式 进入时间 离开时间 体温 进入楼栋, 相关函数输入顺序与之相同
    QSqlQuery query_IO(db);
    query_IO.prepare(create_IO);
    if(!query_IO.exec())
    {
        qDebug() << "创建进出小区登记表失败!";
        success = false;
    }

    //创建家属信息表
    QString create_member = "create table Member(buildingid varchar(10), address varchar(10), ownername varchar(20), name varchar(20), sex varchar(10), "
                            "age int,tel varchar(20), temperature double, healthCondition varchar(10))";
    //家属表属性排序: 楼栋号 门牌号 业主姓名 姓名 性别 年龄 联系方式 体温 健康状况, 相关函数输入参数顺序与之相同
    QSqlQuery query_member(db);
    query_member.prepare(create_member);
    if(!query_member.exec())
    {
        qDebug() << "创建家属信息表失败!";
        success = false;
    }

    //创建购物信息表
    QString create_shopping = "create table Shopping(buildingid varchar(10), address varchar(10), ownername varchar(20), tel varchar(20),"
                              "name varchar(20),number varchar(10), ordertime varchar(20), arrivetime varchar(20), statu varchar(10))";
    //购物信息表属性排序: 楼栋号 门牌号 业主名 联系方式 商品名 数量 下单时间 送达时间 送达状态, 相关函数输入参数顺序与之相同
    QSqlQuery query_Shopping(db);
    query_Shopping.prepare(create_shopping);
    if(!query_Shopping.exec())
    {
        qDebug() << "创建购物信息表失败!";
        success = false;
    }
    return success;
}

void DataBase::Update(QString tablename, QString attributeName, QString newValue, QString keyValue)
{
    if(attributeName == "temperature")
    {
       newValue.toDouble();
    }
    if(attributeName == "age")
    {
       newValue.toInt();
    }
    QSqlDatabase db = QSqlDatabase::database("sqlite1.db");
    QString update = "update "+tablename+" set "+attributeName+"=:new where name=:name";
    QSqlQuery query(db);
    query.prepare(update);
    query.bindValue(":name",keyValue);
    query.bindValue(":new",newValue);
    if(!query.exec())
    {
        qDebug() << "fail";
    }
    else
    {
        qDebug() << "success";
    }
}

void DataBase::Delete(QString tablename, QString keyValue)
{
    QSqlDatabase db = QSqlDatabase::database("sqlite1.db");
    QSqlQuery query(db);
    if(tablename == "Owner")
    {
        QString dele = "delete from Member where ownername=?";
        query.prepare(dele);
        query.addBindValue(keyValue);
        query.exec();
        dele = "delete from Shopping where ownername=?";
        query.prepare(dele);
        query.addBindValue(keyValue);
        query.exec();
    }
    QString dele = "delete from "+tablename+" where name=?";
    query.prepare(dele);
    query.addBindValue(keyValue);
    if(!query.exec())
    {
        qDebug() << "fail";
    }
    else
    {
        qDebug() << "success";
    }
}

QList<QStringList> DataBase::SelectAll(QString tablename)
{
    int index;
    if(tablename == "Owner")
        index = 10;
    else if(tablename == "Staff"||tablename == "Shopping"||tablename == "Member")
        index = 9;
    else
        index = 7;
    QStringList values;
    QList<QStringList> all_values;
    QSqlDatabase db = QSqlDatabase::database("sqlite1.db");
    QString select = "select * from "+tablename;
    qDebug()<<select;
    QSqlQuery query(db);
    query.exec(select);
    while(query.next())
    {
      for(int i=0;i<index;i++)
      {
          values.append(query.value(i).toString());
      }
      all_values.append(values);
      values.clear();
    }
    return all_values;
}

QList<QStringList> DataBase::Select(QString tablename, QString keyValue)
{
    int index;
    if(tablename == "Owner")
        index = 10;
    else if(tablename == "Staff"||tablename == "Shopping"||tablename == "Member")
        index = 9;
    else
        index = 7;
    QStringList values;
    QList<QStringList> all_values;
    QSqlDatabase db = QSqlDatabase::database("sqlite1.db");
    QString select = "select * from "+tablename+" where ownername=:name";
    QSqlQuery query(db);
    query.prepare(select);
    query.bindValue(":name",keyValue);
    qDebug()<<select;
    query.exec();
    while(query.next())
    {
      for(int i=0;i<index;i++)
          values.append(query.value(i).toString());
      all_values.append(values);
      values.clear();
    }
    return all_values;
}

QList<QStringList> DataBase::Select(QString tablename, QString attributeName, QString keyValue)
{
    int index;
    if(tablename == "Owner")
        index = 10;
    else if(tablename == "Staff"||tablename == "Shopping"||tablename == "Member")
        index = 9;
    else
        index = 7;
    QStringList values;
    QList<QStringList> all_values;
    QSqlDatabase db = QSqlDatabase::database("sqlite1.db");
    QString select = "select * from "+tablename+" where "+attributeName+"=:key";
    QSqlQuery query(db);
    query.prepare(select);
    query.bindValue(":key",keyValue);
    qDebug()<<select;
    query.exec();
    while(query.next())
    {
      for(int i=0;i<index;i++)
          values.append(query.value(i).toString());
      all_values.append(values);
      values.clear();
    }
    return all_values;
}

bool DataBase::Login(QString tablename, QString account, QString password, info& person)
{
    QSqlDatabase db = QSqlDatabase::database("sqlite1.db");
    int index;
    (tablename == "Owner")?(index=2):(index=1);
    QString select = "select * from "+tablename+" where account=:a and password=:p";
    QSqlQuery query(db);
    query.prepare(select);
    query.bindValue(":a",account);
    query.bindValue(":p",password);
    query.exec();
    if(query.next())
    {
        person.buildingid = query.value(0).toString();
        person.name = query.value(index).toString();
        if(tablename == "Owner")
        {
            person.address = query.value(1).toString();
            person.Tel = query.value(5).toString();
        }
        return true;
    }
    return false;
}

QString DataBase::IsHealthy(QString ownername)
{
    QString statu;
    QSqlDatabase db = QSqlDatabase::database("sqlite1.db");
    QSqlQuery query(db);
    QString select = "select * from Member where ownername=:name and healthCondition =:h";
    query.prepare(select);
    query.bindValue(":name",ownername);
    query.bindValue(":h","确诊");
    query.exec();
    if(query.next())
    {
        statu = "有确诊患者";
        return statu;
    }
    else
    {
        query.clear();
        select = "select * from Member where ownername=:name and healthCondition =:h";
        query.prepare(select);
        query.bindValue(":name",ownername);
        query.bindValue(":h","疑似");
        query.exec();
        if(query.next())
        {
            statu = "有疑似患者";
            return statu;
        }
        else
        {
            statu = "健康";
            return statu;
        }
    }
}


//业主表操作函数
void DataBase::Insert(QString buildingid,QString address,QString name,QString sex,int age,QString tel,double temperature,QString health)
{
    QString temp = QString::number(temperature,'f',1);
    QSqlDatabase db = QSqlDatabase::database("sqlite1.db");
    QSqlQuery query(db);
    QString select = "select * from Owner where name=:name or buildingid=:id and address =:add";
    query.prepare(select);
    query.bindValue(":name",name);
    query.bindValue(":id",buildingid);
    query.bindValue(":add",address);
    query.exec();
    if(!query.next())
    {
        query.clear();
        QString insert = "insert into Owner values(?,?,?,?,?,?,?,?,?,?)";
        query.prepare(insert);
        query.addBindValue(buildingid);
        query.addBindValue(address);
        query.addBindValue(name);
        query.addBindValue(sex);
        query.addBindValue(age);
        query.addBindValue(tel);
        query.addBindValue(temp);
        query.addBindValue(health);
        query.addBindValue(buildingid.append(address));//账户
        query.addBindValue(tel);//密码
        if(!query.exec())
        {
            qDebug() << "fail";
        }
        else
        {
            qDebug() << "success";
        }
    }
    else
        qDebug() << "已存在";
}

void DataBase::Update(QString name,QString sex,int age,QString tel,double temperature,QString health,QString account,QString password)
{
    QString temp = QString::number(temperature,'f',1);
    QSqlDatabase db = QSqlDatabase::database("sqlite1.db");
    QString update = "update  Owner set sex=:sex,age=:age,tel=:tel,temperature=:tem,healthCondition=:health,"
                     "account=:account,password=:password where name=:name";
    QSqlQuery query(db);
    query.prepare(update);
    query.bindValue(":name",name);
    query.bindValue(":sex",sex);
    query.bindValue(":age",age);
    query.bindValue(":tel",tel);
    query.bindValue(":tem",temp);
    query.bindValue(":health",health);
    query.bindValue(":account",account);
    query.bindValue(":password",password);
    if(!query.exec())
    {
        qDebug() << "fail";
    }
    else
    {
        qDebug() << "success";
    }
}
//void DataBase::Owner_update(QString buildingid,QString address,QString name,QString sex,int age,QString tel,double temperature,QString health)
//{
//    QSqlDatabase db = QSqlDatabase::database("sqlite1.db");
//    QString update = "update  Owner set name=:name,sex=:sex,age=:age,tel=:tel,temperature=:tem,healthCondition=:health"
//                     " where buildingid=:id and address=:address";
//    QSqlQuery query(db);
//    query.prepare(update);
//    query.bindValue(":name",name);
//    query.bindValue(":sex",sex);
//    query.bindValue(":age",age);
//    query.bindValue(":tel",tel);
//    query.bindValue(":tem",temperature);
//    query.bindValue(":id",buildingid);
//    query.bindValue(":health",health);
//    query.bindValue(":address",address);
//    if(!query.exec())
//    {
//        qDebug() << "fail";
//    }
//    else
//    {
//        qDebug() << "success";
//    }
//}
//void DataBase::Owner_delete(QString buildingid,QString address)
//{
//    QSqlDatabase db = QSqlDatabase::database("sqlite1.db");
//    QString dele = "delete from Owner where buildingid=? and address=?";
//    QSqlQuery query(db);
//    query.prepare(dele);
//    query.addBindValue(buildingid);
//    query.addBindValue(address);
//    if(!query.exec())
//    {
//        qDebug() << "fail";
//    }
//    else
//    {
//        qDebug() << "success";
//    }
//}


//物业工作人员操作函数
void DataBase::Insert(QString id,QString name,QString sex,int age,QString tel,double temperature,QString health)
{
    QString temp = QString::number(temperature,'f',1);
    QSqlDatabase db = QSqlDatabase::database("sqlite1.db");
    QString insert = "insert into Staff values(?,?,?,?,?,?,?,?,?)";
    QSqlQuery query(db);
    QString select = "select * from Staff where name=:name";
    query.prepare(select);
    query.bindValue(":name",name);
    query.exec();
    if(!query.next())
    {
        query.clear();
        query.prepare(insert);
        query.addBindValue(id);
        query.addBindValue(name);
        query.addBindValue(sex);
        query.addBindValue(age);
        query.addBindValue(tel);
        query.addBindValue(temp);
        query.addBindValue(health);
        query.addBindValue(id);
        query.addBindValue(tel);
        if(!query.exec())
        {
            qDebug() << "fail";
        }
        else
        {
            qDebug() << "success";
        }
    }
    else
        qDebug() << "已存在";

}

//void DataBase::Stuff_update(QString id, QString name, QString sex, int age, QString tel, double temperature, QString health)
//{
//    QSqlDatabase db = QSqlDatabase::database("sqlite1.db");
//    QString update = "update  Stuff set name=:name,sex=:sex,age=:age,tel=:tel,temperature=:tem,healthCondition=:health"
//                     " where id=:id";
//    QSqlQuery query(db);
//    query.prepare(update);
//    query.bindValue(":name",name);
//    query.bindValue(":sex",sex);
//    query.bindValue(":age",age);
//    query.bindValue(":tel",tel);
//    query.bindValue(":tem",temperature);
//    query.bindValue(":id",id);
//    query.bindValue(":health",health);
//    if(!query.exec())
//    {
//        qDebug() << "fail";
//    }
//    else
//    {
//        qDebug() << "success";
//    }
//}

void DataBase::Update(QString id, QString name, QString sex, int age, QString tel, double temperature, QString health,
                      QString account, QString password)
{
    QString temp = QString::number(temperature,'f',1);
    QSqlDatabase db = QSqlDatabase::database("sqlite1.db");
    QString update = "update  Staff set id=:id,sex=:sex,age=:age,tel=:tel,temperature=:tem,healthCondition=:health,account=:account,"
                     "password=:password where name=:name";
    QSqlQuery query(db);
    query.prepare(update);
    query.bindValue(":name",name);
    query.bindValue(":sex",sex);
    query.bindValue(":age",age);
    query.bindValue(":tel",tel);
    query.bindValue(":tem",temp);
    query.bindValue(":id",id);
    query.bindValue(":health",health);
    query.bindValue(":account",account);
    query.bindValue(":password",password);
    if(!query.exec())
    {
        qDebug() << "fail";
    }
    else
    {
        qDebug() << "success";
    }
}

//void DataBase::Stuff_delete(QString id)
//{
//    QSqlDatabase db = QSqlDatabase::database("sqlite1.db");
//    QString dele = "delete from Stuff where id=? ";
//    QSqlQuery query(db);
//    query.prepare(dele);
//    query.addBindValue(id);
//    if(!query.exec())
//    {
//        qDebug() << "fail";
//    }
//    else
//    {
//        qDebug() << "success";
//    }
//}

//小区外来人口流动表操作函数
void DataBase::Insert(QString date, QString name, QString tel, QString intime, QString outtime, double temperature, QString buildingid)
{
    QString temp = QString::number(temperature,'f',1);
    QSqlDatabase db = QSqlDatabase::database("sqlite1.db");
    QString insert = "insert into IO values(?,?,?,?,?,?,?)";
    QSqlQuery query(db);
    QString select = "select * from IO where tel=:tel and intime=:intime and date=:date";
    query.prepare(select);
    query.bindValue(":tel",tel);
    query.bindValue(":intime",intime);
    query.bindValue(":date",date);
    query.exec();
    if(!query.next())
    {
        query.clear();
        query.prepare(insert);
        query.addBindValue(date);
        query.addBindValue(name);
        query.addBindValue(tel);
        query.addBindValue(intime);
        query.addBindValue(outtime);
        query.addBindValue(temp);
        query.addBindValue(buildingid);
        if(!query.exec())
        {
            qDebug() << "fail";
        }
        else
        {
            qDebug() << "success";
        }
    }
    else
        qDebug() << "已存在";
}

void DataBase::Update(QString attributeName, QString newValue, QString date, QString tel, QString intime)
{
    QSqlDatabase db = QSqlDatabase::database("sqlite1.db");
    QString update = "update  IO set "+attributeName+"=:new where date=:date and tel=:tel and intime=:intime";
    QSqlQuery query(db);
    query.prepare(update);
    query.bindValue(":new",newValue);
    query.bindValue(":date",date);
    query.bindValue(":tel",tel);
    query.bindValue(":intime",intime);
    if(!query.exec())
    {
        qDebug() << "fail";
    }
    else
    {
        qDebug() << "success";
    }
}

void DataBase::IO_delete(QString date, QString tel, QString intime)
{
    QSqlDatabase db = QSqlDatabase::database("sqlite1.db");
    QString dele = "delete from IO where date=? and tel=? and intime=? ";
    QSqlQuery query(db);
    query.prepare(dele);
    query.addBindValue(date);
    query.addBindValue(tel);
    query.addBindValue(intime);
    if(!query.exec())
    {
        qDebug() << "fail";
    }
    else
    {
        qDebug() << "success";
    }
}

//家属表操作函数
void DataBase::Insert(QString buildingid, QString address,QString ownername,QString name, QString sex, int age, QString tel,
                      double temperature, QString health)
{
    QString temp = QString::number(temperature,'f',1);
    QSqlDatabase db = QSqlDatabase::database("sqlite1.db");
    QString insert = "insert into Member values(?,?,?,?,?,?,?,?,?)";
    QSqlQuery query(db);
    QString select = "select * from Member where name=:name";
    query.prepare(select);
    query.bindValue(":name",name);
    query.exec();
    if(!query.next())
    {
        query.clear();
        query.prepare(insert);
        query.addBindValue(buildingid);
        query.addBindValue(address);
        query.addBindValue(ownername);
        query.addBindValue(name);
        query.addBindValue(sex);
        query.addBindValue(age);
        query.addBindValue(tel);
        query.addBindValue(temp);
        query.addBindValue(health);
        if(!query.exec())
        {
            qDebug() << "fail";
        }
        else
        {
            qDebug() << "success";
        }
    }
    else
        qDebug() << "已存在";
}

//void DataBase::Member_update(QString buildingid, QString address, QString name, QString sex, int age, QString tel, double temperature, QString health)
//{
//    QSqlDatabase db = QSqlDatabase::database("sqlite1.db");
//    QString update = "update  Member set name=:name,sex=:sex,age=:age,tel=:tel,temperature=:tem,healthCondition=:health"
//                     " where buildingid=:id and address=:address";
//    QSqlQuery query(db);
//    query.prepare(update);
//    query.bindValue(":name",name);
//    query.bindValue(":sex",sex);
//    query.bindValue(":age",age);
//    query.bindValue(":tel",tel);
//    query.bindValue(":tem",temperature);
//    query.bindValue(":id",buildingid);
//    query.bindValue(":health",health);
//    query.bindValue(":address",address);
//    if(!query.exec())
//    {
//        qDebug() << "fail";
//    }
//    else
//    {
//        qDebug() << "success";
//    }
//}

//void DataBase::Member_delete(QString buildingid, QString address, QString name)
//{
//    QSqlDatabase db = QSqlDatabase::database("sqlite1.db");
//    QString dele = "delete from Member where buildingid=? and address=? and name=?";
//    QSqlQuery query(db);
//    query.prepare(dele);
//    query.addBindValue(buildingid);
//    query.addBindValue(address);
//    query.addBindValue(name);
//    if(!query.exec())
//    {
//        qDebug() << "fail";
//    }
//    else
//    {
//        qDebug() << "success";
//    }
//}


//购物信息表操作函数
void DataBase::Insert(QString buildingid, QString address, QString ownername, QString tel, QString name, QString number, QString ordertime)
{
    QSqlDatabase db = QSqlDatabase::database("sqlite1.db");
    QString insert = "insert into Shopping values(?,?,?,?,?,?,?,?,?)";
    QSqlQuery query(db);
    QString select = "select * from Shopping where name=:name and ownername=:oname and ordertime =:time";
    query.prepare(select);
    query.bindValue(":name",name);
    query.bindValue(":oname",ownername);
    query.bindValue(":time",ordertime);
    query.exec();
    if(!query.next())
    {
        query.clear();
        query.prepare(insert);
        query.addBindValue(buildingid);
        query.addBindValue(address);
        query.addBindValue(ownername);
        query.addBindValue(tel);
        query.addBindValue(name);
        query.addBindValue(number);
        query.addBindValue(ordertime);
        query.addBindValue(NULL);
        query.addBindValue("未送达");
        if(!query.exec())
        {
            qDebug() << "fail";
        }
        else
        {
            qDebug() << "success";
        }
    }
    else
        qDebug() << "已存在";
}

void DataBase::Shopping_update(QString arrivetime, QString tel, QString name, QString ordertime)
{
    QSqlDatabase db = QSqlDatabase::database("sqlite1.db");
    QSqlQuery query(db);
    QString update = "update Shopping set arrivetime=:arrivetime,statu=:statu where name=:name and tel=:tel and ordertime=:time";
    qDebug() << update;
    query.prepare(update);
    query.bindValue(":statu","已送达");
    query.bindValue(":name",name);
    query.bindValue(":arrivetime",arrivetime);
    query.bindValue(":tel",tel);
    query.bindValue(":time",ordertime);
    if(!query.exec())
    {
        qDebug() << "fail";
    }
    else
    {
        qDebug() << "success";
    }
}

void DataBase::Shopping_delete(QString tel,QString name,QString ordertime)
{
    QSqlDatabase db = QSqlDatabase::database("sqlite1.db");
    QString dele = "delete from Shopping where tel=? and name=? and ordertime=?";
    QSqlQuery query(db);
    query.prepare(dele);
    query.addBindValue(tel);
    query.addBindValue(name);
    query.addBindValue(ordertime);
    if(!query.exec())
    {
        qDebug() << "fail";
    }
    else
    {
        qDebug() << "success";
    }
}
